package com.jdone.compus.repository;

import com.jdone.compus.dto.OrderStatsProjection;
import com.jdone.compus.enums.OrderStatus;
import com.jdone.compus.model.BlowUsageRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface BlowUsageRecordRepository extends JpaRepository<BlowUsageRecord, Long>, JpaSpecificationExecutor<BlowUsageRecord> {

    Page<BlowUsageRecord> findAll(Specification<BlowUsageRecord> spec, Pageable pageable);

    Optional<BlowUsageRecord> findTopByDeviceIdOrderByReturnTimeDesc(Long deviceId);

    Optional<BlowUsageRecord> findByBusinessOrderNo(String businessOrderNo);


    /**
     * 按开始/结束时间（可选）统计指定状态订单的总数、总金额
     */
    @Query("SELECT " +
            "   COUNT(b)                   AS totalCount, " +
            "   COALESCE(SUM(b.fee), 0)    AS totalFee " +
            "FROM BlowUsageRecord b " + "WHERE b.orderStatus = :status " +
            "  AND (:startDt IS NULL OR b.borrowTime >= :startDt) " +
            "  AND (:endDt   IS NULL OR b.borrowTime <= :endDt)")
    List<OrderStatsProjection> countAndSumByStatusAndTime(@Param("status") OrderStatus status, @Param("startDt") LocalDateTime startDt, @Param("endDt") LocalDateTime endDt);

    @Query("SELECT FUNCTION('DATE', b.borrowTime) AS day, SUM(b.fee) AS totalFee " + "FROM BlowUsageRecord b " + "WHERE b.orderStatus = :status " + "  AND (:startDt IS NULL OR b.borrowTime >= :startDt) " + "  AND (:endDt   IS NULL OR b.borrowTime <= :endDt) " + "GROUP BY FUNCTION('DATE', b.borrowTime) " + "ORDER BY day")
    List<Object[]> findDailyFeeByStatusAndDateTime(@Param("status") OrderStatus status, @Param("startDt") LocalDateTime startDt, @Param("endDt") LocalDateTime endDt);


    @Query(value = "SELECT DATE_FORMAT(borrow_time, '%Y-%m-%d') AS period, COUNT(*) AS orderCount, COALESCE(SUM(fee),0) AS totalAmount "
            + "FROM blow_usage_records "
            + "WHERE borrow_time >= :start AND borrow_time < :end "
            + "  AND order_status = :status "
            + "GROUP BY period "
            + "ORDER BY period", nativeQuery = true)
    List<Object[]> findStatsByDay(@Param("start") String start,
                                  @Param("end") String end,
                                  @Param("status") String status);

    @Query(value =
            "SELECT DATE_FORMAT(borrow_time, '%Y-%m-%d') AS period, " +
                    "SUM(CASE WHEN order_status = 'PAID' THEN 1 ELSE 0 END) AS created_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'PAID' THEN fee ELSE 0 END),0) AS created_total_amount, " +
                    "SUM(CASE WHEN order_status = 'USED' THEN 1 ELSE 0 END) AS used_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'USED' THEN fee ELSE 0 END),0) AS used_total_amount, " +
                    "SUM(CASE WHEN order_status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'CANCELLED' THEN fee ELSE 0 END),0) AS cancelled_total_amount " +
                    "FROM blow_usage_records " +
                    "WHERE borrow_time >= :start AND borrow_time < :end " +
                    "GROUP BY period " +
                    "ORDER BY period",
            nativeQuery = true)
    List<Object[]> findStatsByDayGroupStatus(@Param("start") String start,
                                             @Param("end") String end);


    @Query(value = "SELECT DATE_FORMAT(borrow_time, '%Y-%m') AS period, COUNT(*) AS orderCount, COALESCE(SUM(fee),0) AS totalAmount "
            + "FROM blow_usage_records "
            + "WHERE borrow_time >= :start AND borrow_time < :end "
            + "  AND order_status = :status "
            + "GROUP BY period "
            + "ORDER BY period", nativeQuery = true)
    List<Object[]> findStatsByMonth(
            @Param("start") String start,
            @Param("end") String end,
            @Param("status") String status);

    @Query(value =
            "SELECT DATE_FORMAT(borrow_time, '%Y-%m') AS period, " +
                    "SUM(CASE WHEN order_status = 'PAID' THEN 1 ELSE 0 END) AS created_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'PAID' THEN fee ELSE 0 END),0) AS created_total_amount, " +
                    "SUM(CASE WHEN order_status = 'USED' THEN 1 ELSE 0 END) AS used_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'USED' THEN fee ELSE 0 END),0) AS used_total_amount, " +
                    "SUM(CASE WHEN order_status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_total, " +
                    "COALESCE(SUM(CASE WHEN order_status = 'CANCELLED' THEN fee ELSE 0 END),0) AS cancelled_total_amount " +
                    "FROM blow_usage_records " +
                    "WHERE borrow_time >= :start AND borrow_time < :end " +
                    "GROUP BY period " +
                    "ORDER BY period",
            nativeQuery = true)
    List<Object[]> findStatsByMonthGroupStatus(@Param("start") String start,
                                               @Param("end") String end);

    @Query("select min(b.borrowTime) from BlowUsageRecord b")
    LocalDateTime findMinBorrowTime();

    @Query("select max(b.borrowTime) from BlowUsageRecord b")
    LocalDateTime findMaxBorrowTime();

}

